package com.qq.first_project.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.qq.first_project.entity.Product;
import com.qq.first_project.entity.vo.ProductVo;
import com.qq.first_project.mapper.ProductMapper;
import com.qq.first_project.service.ProductService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author Qiu
 * @since 2022-04-06
 */
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements ProductService {

    @Autowired
    @Resource
    private ProductMapper productMapper;

    //导出
    @Override
    public void excelExport() throws Exception{
        //从数据库获取数据封装到list集合中
        List<Product> productList =productMapper.selectList(null);

        //设置导出的路径
        String url = "D:\\product.xlsx";

        //设置导出的流文件
        OutputStream os = new FileOutputStream(url);

        //设置工作空间
        Workbook wb = new XSSFWorkbook();

        //设置选项卡
        Sheet sheet = wb.createSheet("sheet1");

        //设置列名
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("type_id");
        row.createCell(2).setCellValue("name");
        row.createCell(3).setCellValue("price");
        row.createCell(4).setCellValue("create_time");
        row.createCell(5).setCellValue("update_time");
        row.createCell(6).setCellValue("total_num");
        row.createCell(7).setCellValue("remark");
        row.createCell(8).setCellValue("version");
        row.createCell(9).setCellValue("deleted");

        //遍历集合，将数据打印到每一行的每个单元格，从第二行开始遍历
        int i=1;
        for(Product product : productList){
            Row row1 = sheet.createRow(i);
            row1.createCell(0).setCellValue(product.getId());
            row1.createCell(1).setCellValue(product.getTypeId());
            row1.createCell(2).setCellValue(product.getName());
            row1.createCell(3).setCellValue(product.getPrice());
            System.out.println(product.getCreateTime());
            row1.createCell(4).setCellValue(product.getCreateTime());
            row1.createCell(5).setCellValue(product.getUpdateTime());
            row1.createCell(6).setCellValue(product.getTotalNum());
            row1.createCell(7).setCellValue(product.getRemark());
            row1.createCell(8).setCellValue(product.getVersion());
            row1.createCell(9).setCellValue(product.getDeleted());

            i++;
        }
        //导出文件
        wb.write(os);
    }

    //导入
    @Override
    public void importExcel(MultipartFile excelFile) throws Exception {
        //创建工作空间
        Workbook wb = WorkbookFactory.create(excelFile.getInputStream());

        //获取选项卡
        Sheet sheet = wb.getSheet("sheet1");

        //获取总行数
        int line=sheet.getLastRowNum();

        //遍历数据，将每个单元格的数据存入对象中，从第二行开始，第一行为列名
        for(int i=1;i<=line;i++){
            DataFormatter formatter = new DataFormatter();

            String typeId = formatter.formatCellValue(sheet.getRow(i).getCell(0));
            String name = formatter.formatCellValue(sheet.getRow(i).getCell(1));
            double price = sheet.getRow(i).getCell(2).getNumericCellValue();
            double totalNum = sheet.getRow(i).getCell(3).getNumericCellValue();
            String remark = formatter.formatCellValue(sheet.getRow(i).getCell(4));
            String version = formatter.formatCellValue(sheet.getRow(i).getCell(5));

            String deleted = formatter.formatCellValue(sheet.getRow(i).getCell(6));
//            Date createTime = sheet.getRow(i).getCell(7).getDateCellValue();
//            Date updateTime = sheet.getRow(i).getCell(8).getDateCellValue();



//            Product product = new Product(typeId,name,(int) price,(int) totalNum,remark,version,deleted, createTime, updateTime);
            Product product = new Product( Long.parseLong(typeId) ,name,(int) price,(int) totalNum,remark,version,deleted);
            productMapper.insert(product);
        }
    }

    // 根据id查询
    @Override
    public Product getProductById(Long id) {
        return productMapper.getProductById(id);
    }

    // 查询所有
    @Override
    public IPage<ProductVo> queryProductList(Page<ProductVo> page, String name) {
        return productMapper.queryProductList(page,name);
    }

}
